package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IElementDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.ElementCreationDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.ElementSearchDAOException;
import com.che.software.testato.domain.entity.Element;
import com.che.software.testato.domain.entity.creation.ElementCreation;
import com.che.software.testato.domain.entity.creation.TransitionCreation;
import com.che.software.testato.domain.entity.search.ElementSearch;
import com.che.software.testato.domain.enumeration.creation.ElementCreationTypes;

/**
 * JDBC implementation of the DAO interface dedicated to the elements
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IElementDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("elementDAO")
public class ElementDAO extends AbstractDAO implements IElementDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(ElementDAO.class);

	/**
	 * Creates a procedural diagram from a testCaseId, a set of elements and a
	 * set of transitions. If activities have been reused or not to create this
	 * diagram.
	 * 
	 * @param testCaseId the test case id.
	 * @param elements the set of elements.
	 * @param transitions the set of transitions.
	 * @throws ElementCreationDAOException if an error occurs during the
	 *         creation.
	 */
	@Override
	public void createDiagram(int testCaseId, List<ElementCreation> elements, List<TransitionCreation> transitions) throws ElementCreationDAOException {
		LOGGER.debug("createDiagram(" + testCaseId + ", " + elements.size() + " elements, " + transitions.size() + " transitions).");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			for (ElementCreation element : elements) {
				Integer activityId = null, pointId = null;
				if (element.getType().equals(ElementCreationTypes.ACTIVITY)) {
					activityId = (Integer) getQueryRunner().query(connection, "SELECT activity_id::int AS activityId FROM activity WHERE label = ? ", new ScalarHandler("activityId"), new Object[] { element.getLabel() });
					if (null == activityId) {
						getQueryRunner().update(connection, "INSERT INTO activity(activity_id, global_description, label) VALUES(nextval('activity_id_seq'), NULL, ?) ", new Object[] { element.getLabel() });
						activityId = (Integer) getQueryRunner().query(connection, "SELECT activity_id::int AS activityId FROM activity WHERE label = ? ", new ScalarHandler("activityId"), new Object[] { element.getLabel() });
					}
				} else {
					getQueryRunner().update(connection, "INSERT INTO point(point_id, point_type, label) VALUES(nextval('point_id_seq'), ?, ?) ", new Object[] { element.getType().name(), element.getLabel() });
					pointId = (Integer) getQueryRunner().query(connection, "SELECT MAX(point_id)::int AS pointId FROM point ", new ScalarHandler("pointId"));
				}
				getQueryRunner().update(connection, "INSERT INTO element(element_id, point_id, activity_id, test_case_id) VALUES(nextval('element_id_seq')," + ((null != activityId) ? "NULL" : "?") + "," + ((null != pointId) ? "NULL" : "?") + ",?) ", (null != activityId) ? new Object[] { activityId, testCaseId } : new Object[] { pointId, testCaseId });
			}
			List<Element> createdElements = getQueryRunner().query(connection, "SELECT element_id AS elementId, point_id AS pointId, activity_id AS activityId, test_case_id AS testCaseId, COALESCE(activity.label, point.label) AS label FROM element LEFT JOIN activity USING(activity_id) LEFT JOIN point USING(point_id) WHERE test_case_id = ? ", new BeanListHandler<Element>(Element.class), new Object[] { testCaseId });
			for (TransitionCreation transition : transitions) {
				boolean source = false, target = false;
				for (Element element : createdElements) {
					if (element.getLabel().equalsIgnoreCase(transition.getSource())) {
						transition.setSourceId(element.getElementId());
						source = true;
					}
					if (element.getLabel().equalsIgnoreCase(transition.getTarget())) {
						transition.setTargetId(element.getElementId());
						target = true;
					}
					if (source && target) {
						break;
					}
				}
				getQueryRunner().update(connection, "INSERT INTO transition(transition_id, target_element, source_element, test_case_id, label) VALUES(nextval('transition_id_seq'), ?, ?, ?, ?) ", new Object[] { transition.getTargetId(), transition.getSourceId(), testCaseId, (null != transition.getLabel()) ? transition.getLabel() : "" });
			}
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new ElementCreationDAOException(e1);
			}
			throw new ElementCreationDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Checks if some element have been setted for a given case test. In other
	 * words, is able to say if a procedural diagram has already been setted or
	 * not.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param caseTestId the case test id.
	 * @return true if the procedural diagram has already been setted, else
	 *         false.
	 * @since July, 2011.
	 * @throws ElementSearchDAOException if an error occurs during the search.
	 */
	@Override
	public boolean isElementExistingFromCaseTestId(int testCaseId) throws ElementSearchDAOException {
		LOGGER.debug("isElementExistingFromCaseTestId(" + testCaseId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT element_id FROM element WHERE test_case_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { testCaseId });
		} catch (SQLException e) {
			throw new ElementSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Element search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws ElementSearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<Element> searchElement(ElementSearch searchBean) throws ElementSearchDAOException {
		LOGGER.debug("searchElement().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getElementSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Element>(Element.class), params.toArray());
		} catch (SQLException e) {
			throw new ElementSearchDAOException(e);
		} finally {
			DbUtils.closeQuietly(connection);
		}
	}

	/**
	 * Recovery of the element search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getElementSearchQueryFromCriterion(ElementSearch searchBean, List<Object> params) {
		LOGGER.debug("getElementSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT element_id AS elementId, point_id AS pointId, activity_id AS activityId, test_case_id AS testCaseId, COALESCE(activity.label, point.label) AS label, point_type AS pointTypeString FROM element LEFT JOIN activity USING(activity_id) LEFT JOIN point USING(point_id) ");
		if (null != searchBean && 0 != searchBean.getParentVariantId()) {
			sBuilder.append("JOIN variant_element USING(element_id) ");
		}
		if (null != searchBean && 0 != searchBean.getParentTestCaseId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("test_case_id = ? ");
			params.add(searchBean.getParentTestCaseId());
		}
		if (null != searchBean && 0 != searchBean.getParentVariantId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("variant_id = ? ");
			params.add(searchBean.getParentVariantId());
		}
		return sBuilder.toString();
	}
}